package net.comm.oper.db;

import org.sakaiproject.db.api.iServiceSql;


/**
 * methods for accessing user data in a database.
 */
public class OperLogServiceSqlMysql implements iServiceSql
{
	/**
	 * 返回根据主键删除语句
	 */
	public String getDeleteSql()
	{
		return "delete from OperLog where OperID=?";
	}

	/**
	 * 返回插入语句
	 */
	public String getInsertSql()
	{
		return "insert into OperLog (OperID, UserID,type, Content, MTime) values (?,?,?,?,?)";
	}
	
	/**
	 * 返回根据主键修改语句
	 */
	public String getUpdateSql()
	{
		return "update OperLog  set UserID=?, type=?, Content=?,  MTime=? where OperID=?";
	}

	/**
	 * 返回根据主键查询语句
	 */
	public String getbykeySql()
	{
		return "select OperID, UserID,type, Content, MTime from OperLog where OperID=?";
	}
	
	/**
	 * 返回根据会员ID查询语句 
	 */
	public String getByUserIDSql()
	{
		StringBuffer sb =  new StringBuffer("select OperID, UserID,type, Content, MTime " +
				" from OperLog");
		
		sb.append(" where UserID=? ");
				
		return sb.toString();
	}
	
	/**
	 * return the sql statement which updates an external user id for a given user in the sakai_user_id_map table.
	 */
	public String getRecbyPageSql()
	{
	
		StringBuffer sb =  new StringBuffer("select OperID, UserID,type, Content, MTime from OperLog ");
		
		sb.append(" where MTime>? Order by MTime Desc ");
		sb.append(" limit ?, ? ");
				
		return sb.toString();	
	}	
	/**
	 * return the sql statement which updates an external user id for a given user in the sakai_user_id_map table.
	 */
	public String getRecSql()
	{
	
		StringBuffer sb =  new StringBuffer("select OperID, UserID,type, Content, MTime from OperLog ");
		
		return sb.toString();	
	}	
	
	/**
	 * return the sql statement which updates an external user id for a given user in the sakai_user_id_map table.
	 */
	public String getRecbyFriendIDSql()
	{
	
		StringBuffer sb =  new StringBuffer("select OperID, UserID,type, Content, MTime from OperLog ");
		
		sb.append(" where UserID in (select FriendID from Friends where UserID=?) ");
		sb.append(" order by OperID desc  limit ?, ? ");
				
		return sb.toString();	
	}

	/**
	 * return the sql statement which updates an external user id for a given user in the sakai_user_id_map table.
	 */
	public String getRecbyLinjuIDSql()
	{
	
		StringBuffer sb =  new StringBuffer("select OperID, UserID,type, Content, MTime from OperLog ");
		
		sb.append(" where UserID in (select LinjuID from Linju where UserID=?) ");
		sb.append(" order by OperID desc  limit ?, ? ");
				
		return sb.toString();	
	}

	/**
	 * return the sql statement which updates an external user id for a given user in the sakai_user_id_map table.
	 */
	public String getRecbyGroupIDSql()
	{
	
		StringBuffer sb =  new StringBuffer("select OperID, UserID,type, Content, MTime from OperLog ");
		
		sb.append("where type in (4,6,7,9)  and UserID in (select UserID from GMember where " );
		sb.append(" GroupID in (select GroupID from GMember where UserID = ? ))");
		sb.append(" order by OperID desc  limit ?, ? ");
				
		return sb.toString();	
	}

	/**
	 * return the sql statement which updates an external user id for a given user in the sakai_user_id_map table.
	 */
	public String getRecbyShequSql()
	{
	
		StringBuffer sb =  new StringBuffer("select OperID, UserID,type, Content, MTime from OperLog ");
		
		sb.append("where Type = 8 and UserID in (select UserID from UserHome where " );
		sb.append(" VillageID in (select VillageID from UserHome where UserID = ? ))");
		sb.append(" order by OperID desc limit ?, ? ");
				
		return sb.toString();	
	}

	/**
	 * 返回所有记录数
	 */
	public String getCountSql1()
	{
		return "select count(*) as COUNT from OperLog ";
	}

	/**
	 * 返回根据查询条件的记录数
	 */
	public String getCountSql2()
	{
		return "select count(*) as COUNT from OperLog where " +
				"UserID in (select FriendID from Friends where UserID=?) ";
	}
	
	/**
	 * 返回根据查询条件的记录数
	 */
	public String getCountSql3()
	{
		return "select count(*) as COUNT from OperLog where " +
				"UserID in (select LinjuID from Linju where UserID=?) ";
	}

	/**
	 * 返回根据查询条件的记录数
	 */
	public String getCountSql4()
	{
		return "select count(*) as COUNT from OperLog " +
				"where type in (4,6,7,9)  and UserID in (select UserID from GMember where " +
				" GroupID in (select GroupID from GMember where UserID = ? )) ";
	}

	/**
	 * 返回根据查询条件的记录数
	 */
	public String getCountSql5()
	{
		return "select count(*) as COUNT from OperLog " +
				"where Type = 8 and UserID in (select UserID from UserHome where " +
				" VillageID in (select VillageID from UserHome where UserID = ? )) ";
	}
	
	/**
	 * @see net.comm.job.db.iServiceSql.impl.UserServiceSql#getMaxInputsForSelectWhereInQueries()
	 */
	public int getMaxInputsForSelectWhereInQueries() {
		// For Oracle, the maximum supported number of expressions in a list is 1000. 
		return 1000;
	}
}
